package sctu.edu.java.year2016.team02.huangguojing;

import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Eoly on 2016/6/11.
 */
public class SQLOperate {
    private static String ipAddress = "localhost";
    private static int port = 3306;
    private static String databasName="null";
    private static String characterEncoding = "utf8";
    private static boolean useSSL = true;
    private static String url = "jdbc:mysql://"+ipAddress+":"+port+"?useSSL="+useSSL;
    private static String user = "root";
    private static String keys = "";
    private static Connection conn;
    private static int connectTimes=0;

    //获得数据库连接
    public static Connection getConn() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, keys);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            if(e.getMessage().equals("Access denied for user 'root'@'localhost' (using password: YES)")){
                outDatabaseSetting();
            }else {
                JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "连接数据库失败", JOptionPane.ERROR_MESSAGE);
                System.exit(-1);
            }
        }
        connectTimes++;
        return conn;
    }

    //对数据库进行删除操作
    //keyvalue：表主关键字内容；key：表主关键字；TableName：表名；
    public static void delete(String key,String keyvalue,String TableName){
        String sql = "DELETE FROM "+TableName+" WHERE "+key+" = " + addQuote(keyvalue);
        SQLexecute(sql);
    }

    //对数据库进行更新操作（value为String型）
    public static void update(String TableName, String name, String key,String keyfield ,String value){
        String sql = "UPDATE "+TableName+" SET " +
                name + "=" + addQuote(value) +
                " WHERE "+keyfield+"=" + addQuote(key);
        SQLexecute(sql);
    }

    //对数据库进行更新操作（value为int型）
    public static void update(String TableName,String name, String key,String keyfield, int value){
        String sql = "UPDATE "+TableName+" SET " +
                name + "=" + value +
                " WHERE "+keyfield+"=" + addQuote(key);
        SQLexecute(sql);
    }

    //对数据库进行插入操作，并返回是否操作成功
    //TableName：表名；name：每列关键字；value：对应关键字插入值；
    public static boolean insert(String TableName, String[] name, String[] value){
        if (name.length == value.length) {
            String sql = "INSERT INTO " + TableName + " (";
            for (int i = 0; i < name.length; i++) {
                sql = sql + name[i] + (i == name.length - 1 ? ") VALUE(" : ",");
            }
            for (int i = 0; i < name.length; i++) {
                sql = sql + addQuote(value[i]) + (i == value.length - 1 ? ") " : ",");
            }
            SQLexecute(sql);
            return true;
        } else return false;
    }

    //对数据库进行插入操作（注明key的位置），并返回是否操作成功
    //TableName：表名；name：每列关键字；value：对应关键字插入值；key：表的主关键字在name的索引号位置；
    public static boolean insert(String TableName, String[] name, String[] value, int keyindex){
        String sql;
        try {
            if (!isNullTable(TableName)) {
                sql = "select " + name[keyindex] + " from " + TableName;
                Statement stmt = getStatement();
                ResultSet rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    if(rs.getString(name[keyindex]).equals(value[keyindex])) {
                        return false;
                    }
                }
                stmt.close();
            }
        }catch (SQLException e){
            JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "数据库插入失败", JOptionPane.WARNING_MESSAGE);
        }

        if (insert(TableName,name,value))
            return true;
        else return false;
    }

    //对数据库进行查找操作，并返回一个从服务端获得的List集合
    //TableName：表名；fieldname：每列关键字；value：对应关键字插入值；isLike：是否为模糊查找；
    public static List<Object[]> select (String TableName,String[] fieldname,String[] value,boolean isLike){
        List list = new ArrayList<>();
        String sql;
        int count=0;
        if (!fieldname[0].equals("")) {
            sql = "SELECT *FROM " + TableName + " WHERE ";
            for (int i = 0; i < fieldname.length; i++) {
                sql = sql + fieldname[i] + (isLike ? " LIKE " : " =") +
                        addPercentSigns(value[i], isLike) + (i == fieldname.length - 1 ? "" : " or ");
            }
        } else {
            sql = "SELECT *FROM " + TableName;
        }
        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                Object[] result = new Object[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    result[i - 1] = rs.getObject(i);
                }
                list.add(count,result);
                count++;
            }
            stmt.close();
        }catch (SQLException e){
            JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "数据库查询失败", JOptionPane.WARNING_MESSAGE);
        }
        return list;
    }

    //创建数据库
    public static void createDatabase(String databasName){
        SQLOperate.databasName =databasName;
        String sql = "create database if not exists "+databasName+" DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
        SQLexecute(sql);
        SQLOperate.url="jdbc:mysql://"+ipAddress+":"+port+"/"+databasName+"?characterEncoding="+characterEncoding+"&useSSL="+useSSL;
        conn=null;
    }

    //创建表
    public static void createTable(String TableName, String... field){
        String sql = "CREATE TABLE if not exists " + TableName + " ( ";
        for (int i = 0; i < field.length; i++) {
            sql = sql + field[i] + (i == field.length - 1 ? ") " : ",");
        }
        SQLexecute(sql);
    }

    //实现sql语句在数据库的实现，仅限增删改
    private static void SQLexecute(String sql){
        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        }catch (SQLException e){
            JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "数据库操作失败", JOptionPane.WARNING_MESSAGE);
        }
    }

    //获得数据库连接的Statement
    private static Statement getStatement(){
        Statement stmt=null;
        createDatabase(databasName);
        while (conn == null){
            if(connectTimes<4) {
                getConn();
            }else {
                JOptionPane.showMessageDialog(new JFrame(), "连接失败", "数据库查询失败", JOptionPane.WARNING_MESSAGE);
                System.exit(-1);
            }
        }
        try {
            stmt = conn.createStatement();
        }catch (SQLException e){
            JOptionPane.showMessageDialog(new JFrame(), e.getMessage(), "获取输入平台失败", JOptionPane.WARNING_MESSAGE);
        }
        return stmt;
    }

    //判断数据库对应表是否为空表
    public static boolean isNullTable(String TableName) throws SQLException {
        String sql = "select count(*) from "+TableName;
        Statement stmt = getStatement();
        ResultSet rs = stmt.executeQuery(sql);
        rs.next();
        if(rs.getInt(1)==0)return true;
        else return false;
    }

    //对一个字符串增加百分号和单引号，如：'%string%'
    private static String addPercentSigns(String str,boolean isLike){
        return isLike?addQuote("%"+str+"%"):addQuote(str);
    }

    //对一个字符串单引号，如：'string'
    private static String addQuote(String str) {
        return new String("'" + str + "'");
    }

    //对数据库用户密码进行设置
    private static void outDatabaseSetting(){
        JDialog setS = new JDialog(new JFrame(),"连接数据库设置",true);
        JLabel userLabel = new JLabel("用户名+密码:");
        JTextField username = new JTextField(user);
        JTextField key = new JTextField(keys);
        JButton sure =  new JButton("确定");
        JButton exit =  new JButton("退出程序");
        JLabel tip = new JLabel("欲连接数据库："+databasName);
        tip.setBounds(20, 10, 200, 25);
        userLabel.setBounds(20, 40, 90, 25);
        username.setBounds(100, 40, 80, 25);
        key.setBounds(185, 40, 50, 25);
        sure.setBounds(20, 80, 100, 25);
        exit.setBounds(150,80,100,25);
        sure.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                SQLOperate.user = username.getText();
                SQLOperate.keys = key.getText();
                setS.dispose();
            }
        });
        exit.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                System.exit(0);
            }
        });
        setS.setBounds(500,500, 300, 150);
        setS.setLayout(null);
        setS.add(exit);
        setS.add(username);
        setS.add(tip);
        setS.add(userLabel);
        setS.add(key);
        setS.add(sure);
        setS.setResizable(false);
        setS.setVisible(true);
    }
}
